Mikmak MySQL DML OrderStatus

CRUD stored procedures voor de OrderStatustabel. De afspraak is dat de namen voor de stored procedurs beginnen met de naam van de tabel gevolgd door de naam van de CRUD handeling. De naam wordt in pascalnotatie geschreven.

Probleem

We moeten de gegevens van een basiseenheid kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de naam. Voor het maken van de stored procedures baseren we ons op het Mikmak logisch model.

Design

Oplossing

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML Insert Stored Procedure for OrderStatus 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS OrderStatusInsert;
DELIMITER //
CREATE PROCEDURE `OrderStatusInsert`
(
	IN pName NVARCHAR (255) ,
	IN pDescription NVARCHAR (1024) ,
	OUT pId INT 
)
BEGIN
INSERT INTO `OrderStatus`
	(
		`OrderStatus`.`Name`,
		`OrderStatus`.`Description`
	)
	VALUES
	(
		pName,
		pDescription
	);
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML Update Stored Procedure for OrderStatus
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS OrderStatusUpdate;
DELIMITER //
CREATE PROCEDURE `OrderStatusUpdate`
(
	pName NVARCHAR (255) ,
	pDescription NVARCHAR (1024) ,
	pId INT 
)
BEGIN
UPDATE `OrderStatus`
	SET
		`Name` = pName,
		`Description` = pDescription
	WHERE `OrderStatus`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML Delete Stored Procedure for OrderStatus 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS OrderStatusDelete;
DELIMITER //
CREATE PROCEDURE `OrderStatusDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `OrderStatus`
	WHERE `OrderStatus`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML SelectOne Stored Procedure for OrderStatus 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS OrderStatusSelectOne;
DELIMITER //
CREATE PROCEDURE `OrderStatusSelectOne`
(
	 pId INT 
)
BEGIN
SELECT * FROM `OrderStatus`
	WHERE `OrderStatus`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML SelectAll Stored Procedure for table OrderStatus 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS OrderStatusSelectAll;
DELIMITER //
CREATE PROCEDURE `OrderStatusSelectAll`
(
)
BEGIN
SELECT `OrderStatus`.`Name`,
	`OrderStatus`.`Id`
	FROM `OrderStatus`
	ORDER BY `Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML SelectByName Stored Procedure for table OrderStatus
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS OrderStatusSelectByName;
DELIMITER //
CREATE PROCEDURE `OrderStatusSelectByName`
(
	 pName NVARCHAR (255) 
)
BEGIN
SELECT `OrderStatus`.`Name`,
	`OrderStatus`.`Id`

	FROM `OrderStatus`
	WHERE `OrderStatus`.`Name` = pName
	ORDER BY `OrderStatus`.`Name`;
END //
DELIMITER ;